﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VNPT.Utility.Oracle;
using VNPT.Data;

namespace VNPT.DoiSoat
{
    public partial class uDongBoMaMang : Core.uBase
    {
        public uDongBoMaMang()
        {
            InitializeComponent();
        }
       
        public override void LoadSetting()
        {
            base.LoadSetting();
        }
       
        public override void SaveSetting()
        {
            base.SaveSetting();
        }

        private void btnMaMang_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            using (var dlg = new OpenFileDialog())
            {
                dlg.InitialDirectory = DoiSoatSetting.Folder_LUU;
                dlg.Filter = "Visual FoxPro File(*.dbf)|*.dbf|All file(*.*)|*.*";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    btnMaMang.Text = dlg.FileName;
                    string SourceStr1 = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
                    using (DbNetData Db = new DbNetData(SourceStr1, DataProvider.OleDb))
                    {
                        Db.Open();
                        var fileName = string.Format("'{0}'", dlg.FileName);
                        if (Db.ColumnExists(fileName, "ma_vung") && Db.ColumnExists(fileName, "ma_mang")
                            && Db.ColumnExists(fileName, "dodai_tb") && Db.ColumnExists(fileName, "mo_ta"))
                        {
                            QueryCommandConfig Query = new QueryCommandConfig(string.Format("SELECT * FROM '{0}'", dlg.FileName));
                            myGridView_Mang.Columns.Clear();
                            myGridControl_Mang.DataSource = Db.GetDataTable(Query);
                            Db.Close();
                            myGridView_Mang.BestFitColumns();
                        }
                        else
                        {
                            DevExpress.XtraEditors.XtraMessageBox.Show("Cấu trúc file [MÃ MẠNG] chưa đúng, vui lòng kiểm tra lại!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            myGridView_Mang.Columns.Clear();
                        }
                    }
                }
            }
            return;
        }

        private void btnDichVu_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            using (var dlg = new OpenFileDialog())
            {
                dlg.InitialDirectory = DoiSoatSetting.Folder_LUU;
                dlg.Filter = "Visual FoxPro File(*.dbf)|*.dbf|All file(*.*)|*.*";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    btnDichVu.Text = dlg.FileName;
                    string SourceStr1 = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
                    using (DbNetData Db = new DbNetData(SourceStr1, DataProvider.OleDb))
                    {
                        Db.Open();
                        var fileName = string.Format("'{0}'", dlg.FileName);
                        if (Db.ColumnExists(fileName, "prefix")
                            && Db.ColumnExists(fileName, "dich_vu")
                            && Db.ColumnExists(fileName, "mo_ta"))
                        {
                            QueryCommandConfig Query = new QueryCommandConfig(string.Format("SELECT * FROM '{0}'", dlg.FileName));
                            myGridView_DichVu.Columns.Clear();
                            myGridControl_DichVu.DataSource = Db.GetDataTable(Query);
                            Db.Close();
                            myGridView_DichVu.BestFitColumns();
                        }
                        else
                        {
                            DevExpress.XtraEditors.XtraMessageBox.Show("Cấu trúc file [DICH VỤ] chưa đúng, vui lòng kiểm tra lại!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            myGridView_DichVu.Columns.Clear();
                        }
                    }
                }
            }
            return;
        }

        private void CapNhatMaMang()
        {
            string SourceStr1 = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            using (DbNetData Db = new DbNetData(SourceStr1, DataProvider.OleDb))
            {
                Db.Open();
                QueryCommandConfig Query = new QueryCommandConfig(string.Format("SELECT * FROM '{0}'", btnMaMang.Text));
                var _Data = Db.GetDataTable(Query);

                var tong = _Data.Rows.Count;
                var i = 0;

                foreach (var item in _Data.Rows)
                {
                    if (bgWork.CancellationPending)
                    {
                        continue;
                    }
                    i++;
                    bgWork.ReportProgress(i * 100 / tong);
                    var tem = ((DataRow)item);
                    CapNhat1MaMang(DbServer, tem);
                }
            }
        }

        private void CapNhatMaDichVu()
        {

            string SourceStr1 = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            using (DbNetData Db = new DbNetData(SourceStr1, DataProvider.OleDb))
            {
                Db.Open();
                QueryCommandConfig Query = new QueryCommandConfig(string.Format("SELECT * FROM '{0}'", btnDichVu.Text));
                var _Data = Db.GetDataTable(Query);

                var tong = _Data.Rows.Count;
                var i = 0;

                    foreach (var item in _Data.Rows)
                    {
                        if (bgWork.CancellationPending)
                        {
                            continue;
                        }
                        i++;
                        bgWork.ReportProgress(i * 100 / tong);
                        var tem = ((DataRow)item);
                        CapNhat1MaDichVu(DbServer, tem);
                    }
            }
        }

        private void CapNhatMaMang_BoSung()
        {
            bgWork.ReportProgress(0);
            if (!chkBoSungMa.Checked)
            {
                bgWork.ReportProgress(100);
                return;
            }
            string sqlString = "\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'3' as Ma_vung, Ma_mang, 'Co dinh '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'8' as Ma_vung, Ma_mang, 'Co dinh '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'6' as Ma_vung, 'VTL'||Ma_mang as Ma_mang, 'VTL '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'2' as Ma_vung, 'VTL'||Ma_mang as Ma_mang, 'EVN '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'5' as Ma_vung, 'SPT'||Ma_mang as Ma_mang, 'SPT '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'4' as Ma_vung, 'VTC'||Ma_mang as Ma_mang, 'VTC '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'7' as Ma_vung, 'FPT'||Ma_mang as Ma_mang, 'FPT '||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'\n" +
            "Union all\n" +
            "select doisoat.loaibo_so_0(ma_Vung)||'1080' as Ma_vung, Ma_mang as Ma_mang, '1080 ' ||ten_Tinh as Mo_Ta from Mavung where Ma_mang <>'HE1HCM'";
          
            QueryCommandConfig Query = new QueryCommandConfig(sqlString);
                var _Data = DbServer.GetDataTable(Query);

                var tong = _Data.Rows.Count;
                var i = 0;

                foreach (var item in _Data.Rows)
                {
                    if (bgWork.CancellationPending)
                    {
                        continue;
                    }
                    i++;
                    bgWork.ReportProgress(i * 100 / tong);
                    
                    var tem1 = ((DataRow)item);

                    CapNhat1MaMang(DbServer, tem1);
                }
        }


        string txtHienThi = "";

        void CapNhat1MaDichVu(DbNetData DB, DataRow item)
        {
            var prefix = (item["prefix"] ?? "").ToString().Trim();
            var dich_vu = (item["dich_vu"] ?? "").ToString().Trim();
            var mo_ta = (item["mo_ta"] ?? "").ToString().Trim();
            txtHienThi = "Đang cập nhật MaDichVu -> " + prefix;
            string QueryExit = string.Format("SELECT Count(*) as SL from MADICHVU Where prefix='{0}'", prefix);
            var queExit = new QueryCommandConfig(QueryExit);
            //queExit.Params.Add("prefix", prefix);

            var sl = DB.ExecuteScalar(queExit).ToString();
            //Kiểm tra nếu rồn tại trước thì cập nhật, ngược lại thì thêm mới
            if (int.Parse(sl) <= 0)
            {
                QueryCommandConfig Query = new QueryCommandConfig("INSERT INTO MADICHVU(prefix, dich_vu, mo_ta,dodai) VALUES (:prefix,:dich_vu,:mo_ta,:dodai)");
                Query.Params.Add("prefix", prefix);
                Query.Params.Add("dich_vu", dich_vu);
                Query.Params.Add("mo_ta", mo_ta);
                Query.Params.Add("dodai", prefix.Length);
                DB.ExecuteNonQuery(Query);
            }
            else
            {
                QueryCommandConfig Query = new QueryCommandConfig("UPDATE MADICHVU SET dich_vu=:dich_vu, mo_ta=:mo_ta, dodai=:dodai Where prefix=:prefix");

                Query.Params.Add("prefix", prefix);
                Query.Params.Add("dich_vu", dich_vu);
                Query.Params.Add("mo_ta", mo_ta);
                Query.Params.Add("dodai", prefix.Length);
                DB.ExecuteNonQuery(Query);

            }

        }

        void CapNhat1MaMang(DbNetData DB, DataRow item)
        {
            var ma_vung = (item["ma_vung"] ?? "").ToString().Trim();
            var ma_mang = (item["ma_mang"] ?? "").ToString().Trim();

            if (ma_vung.StartsWith("0"))
            {
                ma_vung = ma_vung.Substring(1);
            }

            var mo_ta = (item["mo_ta"] ?? "").ToString().Trim();
            txtHienThi = "Đang cập nhật MaMang -> " + ma_vung;


            string QueryExit = string.Format("SELECT Count(*) from MAMANG Where ma_vung='{0}' AND ma_mang='{1}'", ma_vung, ma_mang);
            var queExit = new QueryCommandConfig(QueryExit);
            // queExit.Params.Add("ma_vung", ma_vung);

            var sl = DB.ExecuteScalar(queExit).ToString();
            if (int.Parse(sl) <= 0)
            {
                QueryCommandConfig Query = new QueryCommandConfig(@"INSERT INTO MAMANG(ma_vung, ma_mang, dodai, mo_ta) 
                                                                    VALUES (:ma_vung, :ma_mang, :dodai, :mo_ta) ");
                Query.Params.Add("ma_vung", ma_vung);
                Query.Params.Add("ma_mang", ma_mang);
                Query.Params.Add("dodai", ma_vung.Length);
                Query.Params.Add("mo_ta", mo_ta);
                DB.ExecuteNonQuery(Query);
            }
            else
            {
                //ma_vung=:ma_vung, ma_mang=:ma_mang,
                QueryCommandConfig Query = new QueryCommandConfig(@"UPDATE MAMANG SET  dodai=:dodai, mo_ta=:mo_ta
                                                                     Where ma_vung=:ma_vung  AND ma_mang=:ma_mang");
                Query.Params.Add("ma_vung", ma_vung);
                Query.Params.Add("ma_mang", ma_mang);
                Query.Params.Add("dodai", ma_vung.Length);
                Query.Params.Add("mo_ta", mo_ta);
                DB.ExecuteNonQuery(Query);
            }

        }
      
        private void TruncateTable()
        {
            if (chkTruncateMaMang.Checked)
            {
                DbServer.ExecuteNonQuery(new CommandConfig("TRUNCATE TABLE MAMANG"));
            }

            if (chkTruncateMaDichVu.Checked)
            {
                DbServer.ExecuteNonQuery(new CommandConfig("TRUNCATE TABLE MADICHVU"));
            }
        }
       
        private void LoaiBoMaVung()
        {
            bgWork.ReportProgress(0);
            if (chkLoaiBoMaVung.Checked)
            {
                var sql = "delete from mamang where Ma_vung in ( select loaibo_so_0(Ma_vung) from MaVung where ma_vung is not null)";
                DbServer.ExecuteNonQuery(new CommandConfig(sql));
            }
            bgWork.ReportProgress(100);
        }

        void DocDuLieuServer()
        {
            using (DbNetData Db = new DbNetData(Data.DataSetting.CONNECTSTRING_TNS_NAME, DataProvider.OracleClient))
            {
                Db.Open();
                QueryCommandConfig Query_MaMang = new QueryCommandConfig(string.Format("SELECT * FROM {0}", "MaMang"));
                myGridView_Mang.Columns.Clear();
                myGridControl_Mang.DataSource = Db.GetDataTable(Query_MaMang);
                Db.Close();
                myGridView_Mang.BestFitColumns();


                QueryCommandConfig Query = new QueryCommandConfig(string.Format("SELECT * FROM {0}", "MaDichVu"));
                myGridView_DichVu.Columns.Clear();
                myGridControl_DichVu.DataSource = Db.GetDataTable(Query);
                Db.Close();
                myGridView_DichVu.BestFitColumns();
            }
        }

        private void CreateMaMang(DbNetData Db)
        {
            string sql = @"create table MAMANG
                            (
                              ma_vung  VARCHAR2(15),
                              ma_mang  VARCHAR2(15),
                              dodai NUMBER,
                              mo_ta    VARCHAR2(50)
                            )";
            Db.ExecuteNonQuery(new CommandConfig(sql));
            string sql2 = "create index INDEX_MAMANG on MAMANG (MA_MANG, MA_VUNG)";
            Db.ExecuteNonQuery(new CommandConfig(sql2));
        }

        private void CreateDichVu(DbNetData Db)
        {
            string sql = @"create table MADICHVU
                            (
                              prefix  VARCHAR2(15),
                              dich_vu VARCHAR2(15),
                              mo_ta   VARCHAR2(50),
                              dodai   NUMBER
                            )";
            Db.ExecuteNonQuery(new CommandConfig(sql));
            string sql2 = "create index MADICHVU_PREFIX on MADICHVU (PREFIX)";
            Db.ExecuteNonQuery(new CommandConfig(sql2));
        }

        private void TruocKhiChay()
        {
            var m = this.Parent as DevExpress.XtraEditors.PanelControl;
            if (m != null)
            {
                m.Enabled = false;
            }

            DevExpress.XtraEditors.XtraForm f = null;
            if ((this.Parent as DevExpress.XtraEditors.XtraForm) != null)
            {
                f = this.Parent as DevExpress.XtraEditors.XtraForm;
            }
            else
            {
                if ((this.Parent.Parent as DevExpress.XtraEditors.XtraForm) != null)
                {
                    f = this.Parent.Parent as DevExpress.XtraEditors.XtraForm;
                }
                else
                {
                    if ((this.Parent.Parent.Parent as DevExpress.XtraEditors.XtraForm) != null)
                    {
                        f = this.Parent.Parent.Parent as DevExpress.XtraEditors.XtraForm;
                    }
                }

            }

            if (f != null)
            {
                f.FormClosing += (a1, e1) =>
                {
                    if (bgWork.IsBusy)
                    {
                        string text = "Chương trình đang chạy, không thể đóng form! Vui lòng hủy trước khi đóng chương trình?";
                        string caption = "Xác nhận?";
                        DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        e1.Cancel = true;
                    }
                };

            }
        }

        private void SauKhiChay()
        {
            var m = this.Parent as DevExpress.XtraWizard.WizardPage;
            if (m != null)
            {
                m.AllowNext = true;
                m.AllowBack = true;
            }
        }

        private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            TruncateTable();
            CapNhatMaDichVu();
            CapNhatMaMang();
            CapNhatMaMang_BoSung();
            LoaiBoMaVung();
        }

        private void bgWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBarControl1.EditValue = e.ProgressPercentage;
            lblHienThi.Text = txtHienThi;
        }

        private void bgWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            lblHienThi.Text = "Xong";
            SauKhiChay();
            DocDuLieuServer(); if (e.Error != null)
            {
                // AppendTextBox(e.Error.Message);
                DevExpress.XtraEditors.XtraMessageBox.Show("Hoàn thành, có phát sinh lỗi.\nVui lòng kiểm tra lại!\n" + e.Error.Message + e.Error.Message, "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Đã thành công!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                // AppendTextBox("Hoàn thành!");
            }
            SauKhiChay();
        }
       
        private void btnThucHien_Click(object sender, EventArgs e)
        {
            if (!DbServer.TableExists("MAMANG"))
            {
                if (DevExpress.XtraEditors.XtraMessageBox.Show("Chưa có table [MAMANG], bạn có muốn tạo mới không?", "Xác nhận!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    CreateMaMang(DbServer);
                }
                else
                {
                    return;
                }
            }
            if (!DbServer.TableExists("MADICHVU"))
            {
                if (DevExpress.XtraEditors.XtraMessageBox.Show("Chưa có table [MADICHVU], bạn có muốn tạo mới không?", "Xác nhận!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    CreateDichVu(DbServer);
                }
                else
                {
                    return;
                }
            }
            if (!bgWork.IsBusy)
            {
                TruocKhiChay();
                bgWork.RunWorkerAsync();
            }
            else
            {
                string text = "Đang cập nhật, vui lòng kiểm tra lại hoặc hủy bỏ quá trình!";
                string caption = "Thông báo!";
                DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        private void btnDung_Click(object sender, EventArgs e)
        {
            if (this.bgWork.IsBusy)
            {
                string text = "Bạn chắc có muốn dừng thực hiện không?";
                string caption = "Hủy bỏ?";
                if (DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    this.bgWork.CancelAsync();
                }
            }
        }

        private void hpConnect_OpenLink(object sender, DevExpress.XtraEditors.Controls.OpenLinkEventArgs e)
        {
            new Data.fOracleConnect_TNS().ShowDialog();
        }

        private void mySimpleButton1_Click(object sender, EventArgs e)
        {
            CapNhatMaMang_BoSung();
        }


    }
}
